﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;

namespace DSS_Simplex
{
    class FileCmd
    {
        private static MySqlConnection connection;
        private static string ConnectionString = "Server=localhost;Database=dss;Uid=root;Pwd=;";
        private static DataGridView dgvToSave1, dgvToSave2, dgvToSave3;

        public static void TemporarySaveDGV(DataGridView dataGridView1, DataGridView dataGridView2, DataGridView dataGridView3)
        {
            dgvToSave1 = dataGridView1;
            dgvToSave2 = dataGridView2;
            dgvToSave3 = dataGridView3;
        }

        #region Save Project
        public static void SaveProject(string projectName)
        {
            try
            {
                connection = new MySqlConnection(ConnectionString);
                //sql command object
                string cmdText = "insert into project (nama_proj) VALUES (?nama_proj)";

                MySqlCommand cmd = new MySqlCommand(cmdText, connection);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("?nama_proj", MySqlDbType.VarChar, 200).Value = projectName;
                connection.Open();
                int result = cmd.ExecuteNonQuery();
                connection.Close();
                SaveAs(dgvToSave1, dgvToSave2, dgvToSave3);
                MessageBox.Show("Saved!!");

            }
            catch (Exception ex)
            {
                throw new Exception("Saving project error: "+ex.Message);
            }
        }
       
        public static void BindToDataSet(string cmdText, ref DataSet dataSet)
        {
            try
            {
                MySqlConnection connection = new MySqlConnection(ConnectionString);
                connection.Open();
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = cmdText;
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(dataSet);
            }
            catch (Exception ex)
            {
                throw new Exception("Binding to dataset error: " + ex.Message);
            }
        }
        public static void BindToDataTable(string cmdText, ref DataTable dataTable)
        {
            try
            {
                MySqlConnection connection = new MySqlConnection(ConnectionString);
                connection.Open();
                
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = cmdText;
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(dataTable);

                //connection.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Binding to dataset error: " + ex.Message);
            }
        }
               
        public static void SaveAs(DataGridView dataGridView1, DataGridView dataGridView2, DataGridView dataGridView4)
        {
            try
            {
                connection = new MySqlConnection(ConnectionString);
                //sql command object
                MySqlCommand cmd1 = connection.CreateCommand();
                connection.Open();
                cmd1.CommandText = "SELECT MAX(id_proj) FROM project";
                int id_project = ((int)cmd1.ExecuteScalar());
                Console.WriteLine(id_project);

                int i;
                int j;
                for (i = 0; i < dataGridView1.RowCount - 1; i++)
                {
                    string nama_var = Convert.ToString(dataGridView1.Rows[i].Cells[0].Value);
                    double koef_var = Convert.ToDouble(dataGridView1.Rows[i].Cells[1].Value);
                    double min_var = Convert.ToDouble(dataGridView1.Rows[i].Cells[2].Value);
                    double max_var = Convert.ToDouble(dataGridView1.Rows[i].Cells[3].Value);
                    string deskripsi = Convert.ToString(dataGridView1.Rows[i].Cells[4].Value);

                    //save variable
                    string cmdText = "insert into variable (nama_var,koef_var, min_var, max_var, deskripsi, id_proj) VALUES (@nama_var, @koef_var, @min_var, @max_var, @deskripsi, @id_proj)";
                    MySqlCommand cmd = new MySqlCommand(cmdText, connection);
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@nama_var", nama_var);
                    cmd.Parameters.AddWithValue("@koef_var", koef_var);
                    cmd.Parameters.AddWithValue("@min_var", min_var);
                    cmd.Parameters.AddWithValue("@max_var", max_var);
                    cmd.Parameters.AddWithValue("@deskripsi", deskripsi);
                    cmd.Parameters.AddWithValue("@id_proj", id_project);
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Variabel disimpan");
                }

                for (j = 0; j < dataGridView2.RowCount - 1; j++)
                {
                    string nama_const = Convert.ToString(dataGridView2.Rows[j].Cells[0].Value);
                    double min_const = Convert.ToDouble(dataGridView2.Rows[j].Cells[1].Value);
                    double max_const = Convert.ToDouble(dataGridView2.Rows[j].Cells[2].Value);
                    
                    //save constraint
                    string cmdText2 = "insert into `constraint` (nama_const, min_const, max_const, id_proj) VALUES (@nama_const, @min_const, @max_const, @id_proj)";
                    MySqlCommand cmd2 = new MySqlCommand(cmdText2, connection);
                    cmd2.CommandType = CommandType.Text;
                    cmd2.Parameters.AddWithValue("@nama_const", nama_const);
                    cmd2.Parameters.AddWithValue("@min_const", min_const); 
                    cmd2.Parameters.AddWithValue("@max_const", max_const);
                    cmd2.Parameters.AddWithValue("@id_proj", id_project);
                    cmd2.ExecuteNonQuery();
                    MessageBox.Show("constraint disimpan");
                }
                MessageBox.Show("id projet : " + id_project);
                        
                //save formula
                MySqlCommand cmdvar = connection.CreateCommand();
                cmdvar.CommandText = "SELECT id_var FROM `variable` WHERE id_proj = " + id_project + "";
                //int id_var = ((int)cmdvar.ExecuteScalar());
                MySqlDataReader reader = cmdvar.ExecuteReader();
                List<int> listvar = new List<int>();
                while (reader.Read())
                {
                    try
                    {
                        Console.WriteLine("" + reader);
                        listvar.Add(Convert.ToInt32(reader["id_var"].ToString()));
                    }
                    catch (Exception ex) { MessageBox.Show("errrrr " + ex.Message); }
                }
                reader.Close();

                MySqlCommand cmdconst = connection.CreateCommand();
                cmdconst.CommandText = "SELECT id_const FROM `constraint` WHERE id_proj = " + id_project + "";
                reader = cmdconst.ExecuteReader();
                List<int> listconst = new List<int>();
                while (reader.Read())
                {
                    try
                    {
                        //listconst.Add(int.Parse("" + reader));
                        listconst.Add(Convert.ToInt32(reader["id_const"].ToString()));
                    }
                    catch (Exception ex) { MessageBox.Show("ererererer " + ex.Message); }
                }

                reader.Close();
                Console.WriteLine("jumlah constraint:  " + listconst.Count);
                Console.WriteLine("jumlah variabel:  " + listvar.Count);
                //MessageBox.Show(dataGridView4.Rows[3].Cells[2].Value + "");
                for (i = 0; i < listconst.Count; i++)
                {
                    //MessageBox.Show("b");
                    for (j = 0; j < listvar.Count; j++) 
                    {
                        double value = Convert.ToDouble(dataGridView4.Rows[i].Cells[j + 1].Value);
                       
                        Console.WriteLine("value: " + value);
                        string cmdText3 = "insert into `formula` (id_var, id_const, value, id_proj) VALUES ("+listvar[j]+", "+listconst[i]+", @value, @id_proj)";
                        
                        MySqlCommand cmd3 = new MySqlCommand(cmdText3, connection);
                        cmd3.CommandType = CommandType.Text;

                        cmd3.Parameters.AddWithValue("@value", value);
                        cmd3.Parameters.AddWithValue("@id_proj", id_project);
                        cmd3.ExecuteNonQuery();
                        //MessageBox.Show("a");
                    }
                }
                connection.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        #endregion

        #region SaveAs
        public static void SaveProj(DataGridView dataGridView1, DataGridView dataGridView2, DataGridView dataGridView4, int listItem) 
        {
            connection = new MySqlConnection(ConnectionString);
            //sql command object
            connection.Open();
            string cmdText = "DELETE FROM `variable` WHERE id_proj = " + listItem + " ";
            MySqlCommand cmd = new MySqlCommand(cmdText, connection);
            cmd.ExecuteNonQuery();

            string cmdText1 = "DELETE FROM `constraint` WHERE id_proj = " + listItem + " ";
            MySqlCommand cmd1 = new MySqlCommand(cmdText1, connection);
            cmd1.ExecuteNonQuery();

            string cmdText2 = "DELETE FROM `formula` WHERE id_proj = " + listItem + " ";
            MySqlCommand cmd2 = new MySqlCommand(cmdText2, connection);
            cmd2.ExecuteNonQuery();

            //simpan
            int i;
            int j;
            for (i = 0; i < dataGridView1.RowCount - 1; i++)
            {
                string nama_var = Convert.ToString(dataGridView1.Rows[i].Cells[0].Value);
                double koef_var = Convert.ToDouble(dataGridView1.Rows[i].Cells[1].Value);
                double min_var = Convert.ToDouble(dataGridView1.Rows[i].Cells[2].Value);
                double max_var = Convert.ToDouble(dataGridView1.Rows[i].Cells[3].Value);
                string deskripsi = Convert.ToString(dataGridView1.Rows[i].Cells[4].Value);

                //save variable
                string cmdText3 = "insert into variable (nama_var,koef_var, min_var, max_var, deskripsi, id_proj) VALUES (@nama_var1, @koef_var1, @min_var1, @max_var1, @deskripsi1, @id_proj1)";
                MySqlCommand cmd3 = new MySqlCommand(cmdText3, connection);
                cmd3.CommandType = CommandType.Text;

                cmd3.Parameters.AddWithValue("@nama_var1", nama_var);
                cmd3.Parameters.AddWithValue("@koef_var1", koef_var);
                cmd3.Parameters.AddWithValue("@min_var1", min_var);
                cmd3.Parameters.AddWithValue("@max_var1", max_var);
                cmd3.Parameters.AddWithValue("@deskripsi1", deskripsi);
                cmd3.Parameters.AddWithValue("@id_proj1", listItem);
                cmd3.ExecuteNonQuery();
                MessageBox.Show("Variabel disimpan");
            }

            for (j = 0; j < dataGridView2.RowCount - 1; j++)
            {
                string nama_const = Convert.ToString(dataGridView2.Rows[j].Cells[0].Value);
                double min_const = Convert.ToDouble(dataGridView2.Rows[j].Cells[1].Value);
                double max_const = Convert.ToDouble(dataGridView2.Rows[j].Cells[2].Value);

                //save constraint
                string cmdText4 = "insert into `constraint` (nama_const, min_const, max_const, id_proj) VALUES (@nama_const1, @min_const1, @max_const1, @id_proj1)";
                MySqlCommand cmd4 = new MySqlCommand(cmdText4, connection);
                cmd4.CommandType = CommandType.Text;
                cmd4.Parameters.AddWithValue("@nama_const1", nama_const);
                cmd4.Parameters.AddWithValue("@min_const1", min_const);
                cmd4.Parameters.AddWithValue("@max_const1", max_const);
                cmd4.Parameters.AddWithValue("@id_proj1", listItem);
                cmd4.ExecuteNonQuery();
                MessageBox.Show("constraint disimpan");
            }
            MessageBox.Show("id projet : " + listItem);

            //save formula
            MySqlCommand cmdvar = connection.CreateCommand();
            cmdvar.CommandText = "SELECT id_var FROM `variable` WHERE id_proj = " + listItem + "";
            MySqlDataReader reader = cmdvar.ExecuteReader();
            List<int> listvar = new List<int>();
            while (reader.Read())
            {
                try
                {
                    Console.WriteLine("" + reader);
                    listvar.Add(Convert.ToInt32(reader["id_var"].ToString()));
                }
                catch (Exception ex) { MessageBox.Show("errrrr " + ex.Message); }
            }
            reader.Close();

            MySqlCommand cmdconst = connection.CreateCommand();
            cmdconst.CommandText = "SELECT id_const FROM `constraint` WHERE id_proj = " + listItem + "";
            reader = cmdconst.ExecuteReader();
            List<int> listconst = new List<int>();
            while (reader.Read())
            {
                try
                {
                    listconst.Add(Convert.ToInt32(reader["id_const"].ToString()));
                }
                catch (Exception ex) { MessageBox.Show("ererererer " + ex.Message); }
            }

            reader.Close();
            Console.WriteLine("jumlah constraint:  " + listconst.Count);
            Console.WriteLine("jumlah variabel:  " + listvar.Count);
            //MessageBox.Show(dataGridView4.Rows[3].Cells[2].Value + "");
            for (i = 0; i < listconst.Count; i++)
            {
                //MessageBox.Show("b");
                for (j = 0; j < listvar.Count; j++)
                {
                    double value = Convert.ToDouble(dataGridView4.Rows[i].Cells[j + 1].Value);

                    Console.WriteLine("value: " + value);
                    string cmdText5 = "insert into `formula` (id_var, id_const, value, id_proj) VALUES (" + listvar[j] + ", " + listconst[i] + ", @value1, @id_proj1)";

                    MySqlCommand cmd5 = new MySqlCommand(cmdText5, connection);
                    cmd5.CommandType = CommandType.Text;

                    cmd5.Parameters.AddWithValue("@value1", value);
                    cmd5.Parameters.AddWithValue("@id_proj1", listItem);
                    cmd5.ExecuteNonQuery();
                    //MessageBox.Show("a");
                }
            }
            connection.Close();


        }
        #endregion
       
        #region Open Project
        public static void OpenProj(DataGridView dataGridView1, DataGridView dataGridView2, DataGridView dataGridView4, int listItem) 
        {
            
            //DataTable dt;
            
            DataTable dt1,dt2,dt3;
            try
            {
                int i,j;
                int jmlvar = 0;
                int jmlconst = 0;
                dt1 = new DataTable();
                dt2 = new DataTable();
                dt3 = new DataTable();

                FileCmd.BindToDataTable("SELECT nama_var, koef_var, min_var, max_var, deskripsi FROM `variable` where id_proj ="+listItem+" ORDER BY id_var ASC", ref dt1);
                dataGridView1.Columns[0].DataPropertyName = "nama_var";
                dataGridView1.Columns[1].DataPropertyName = "koef_var";
                dataGridView1.Columns[2].DataPropertyName = "min_var";
                dataGridView1.Columns[3].DataPropertyName = "max_var";
                dataGridView1.Columns[4].DataPropertyName = "deskripsi";
                //dataGridView1.DataSource = dt1;

                for (int row = 0; row < dt1.Rows.Count; ++row)
                {
                    dataGridView1.Rows.Add();
                    for (int col = 0; col < dt1.Columns.Count; col++)
                    {
                        dataGridView1.Rows[row].Cells[col].Value = dt1.Rows[row][col].ToString();
                    }
                }

                FileCmd.BindToDataTable("SELECT nama_const, min_const, max_const FROM `constraint` where id_proj = " + listItem + " ORDER BY id_const ASC", ref dt2);
                dataGridView2.Columns[0].DataPropertyName = "nama_const";
                dataGridView2.Columns[1].DataPropertyName = "min_const";
                dataGridView2.Columns[2].DataPropertyName = "max_const";
                //dataGridView2.DataSource = dt2;
                for (int row = 0; row < dt2.Rows.Count; ++row)
                {
                    dataGridView2.Rows.Add();
                    for (int col = 0; col < dt2.Columns.Count; col++)
                    {
                        dataGridView2.Rows[row].Cells[col].Value = dt2.Rows[row][col].ToString();
                    }
                }

                FileCmd.CountVar(ref jmlvar,ref jmlconst, listItem);
                Console.WriteLine(jmlvar);
                Console.WriteLine(jmlconst);

                dataGridView4.ColumnCount = jmlvar + 1;
                for (j = 1; j < jmlvar + 1; j++)
                {
                    dataGridView4.Columns[j].Name = dataGridView1.Rows[j - 1].Cells[0].Value+"";
                }
               // for (i = 0; i < jmlconst; i++)
                //{
                //    dataGridView4.Rows.Add(dataGridView2.Rows[i].Cells[0].Value+"");
                //}
                MySqlConnection connection;
                string ConnectionString = "Server=localhost;Database=dss;Uid=root;Pwd=;";
                connection = new MySqlConnection(ConnectionString);
                MySqlCommand cmdfor = connection.CreateCommand();
                connection.Open();
                cmdfor.CommandText = "SELECT value FROM `formula` WHERE id_proj = " + listItem + " ORDER BY id_for ASC";
                MySqlDataReader reader = cmdfor.ExecuteReader();
                
                try
                {
                    for (i = 0; i < jmlconst; i++)
                    {

                        for (j = 1; j < jmlvar + 1; j++)
                        {
                            reader.Read();
                            //Console.WriteLine(Convert.ToDouble(reader["value"]));
                            dataGridView4.Rows[i].Cells[j].Value = Convert.ToDouble(reader["value"]);

                        }
                        Console.WriteLine(Convert.ToDouble(reader["value"]));
                    }
                }
                catch (Exception ex) { MessageBox.Show("errrrr " + ex.Message); }
                //}
                reader.Close();
                connection.Close();
                

            }
            catch (Exception ex)
            {
                MessageBox.Show(string.Format(ex.Message), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        public static void CountVar(ref int jmlVar, ref int jmlConst, int listItem)
        {
            connection = new MySqlConnection(ConnectionString);
            MySqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = "SELECT COUNT(id_var) FROM `variable` WHERE id_proj = " + listItem + "";
            connection.Open();
            jmlVar = Convert.ToInt32(cmd.ExecuteScalar());
            //Console.WriteLine(jmlVar);

            MySqlCommand cmd1 = connection.CreateCommand();
            cmd1.CommandText = "SELECT COUNT(id_const) FROM `constraint` WHERE id_proj = " + listItem + "";
            jmlConst = Convert.ToInt32(cmd1.ExecuteScalar());
            //Console.WriteLine(jmlConst);
        }

        #endregion

        #region Excel Stuffs

        private static Excel.Application app = null;
        private static Excel.Workbook workbook = null;
        private static Excel.Worksheet worksheet = null;
        private static Excel.Range workSheet_range = null;

        public static void createDoc()
        {
            try
            {
                object misValue = System.Reflection.Missing.Value;
                app = new Excel.Application();
                //app.Visible = true;
                workbook = app.Workbooks.Add(misValue);
                worksheet = (Excel.Worksheet)workbook.Sheets[1];
                worksheet.Name = "Variabel";
                worksheet = (Excel.Worksheet)workbook.Sheets[2];
                worksheet.Name = "Batasan";
                worksheet = (Excel.Worksheet)workbook.Sheets[3];
                worksheet.Name = "Formula";
            }
            catch (Exception e)
            {
                MessageBox.Show("Failed to create a new Excel file! " + e.Message);
            }
        }

        public static void addData(int row, int col, string data, string cell1, string cell2, string format)
        {
            worksheet.Cells[row, col] = data;
            workSheet_range = worksheet.get_Range(cell1, cell2);
            workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
            workSheet_range.NumberFormat = format;
        }

        public static String getExcelColumnName(int col)
        {
            String temp = "";
            bool lt26 = true;
            while (col >= 26)
            {
                temp = (char)('A' + (col % 26)) + temp;
                col /= 26;
                lt26 = false;
            }
            if (lt26)
                temp = (char)('A' + (col % 26)) + temp;
            else
                temp = (char)('A' + (col % 26) - 1) + temp;
            return temp;
        }

        public static void closeDoc(string xlfile)
        {
            object misValue = System.Reflection.Missing.Value;
            workbook.SaveAs(xlfile, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            workbook.Close(true, misValue, misValue);
            app.Quit();
        }

        #endregion

        public static void SimpanXls(DataGridView dataGridView1, DataGridView dataGridView2) 
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Excel files (*.xls)|*.xls";
            saveFileDialog.FilterIndex = 0;
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.CreatePrompt = true;
            saveFileDialog.Title = "Export Excel File To";
            saveFileDialog.ShowDialog();

            /*
            Stream myStream;
            myStream = saveFileDialog.OpenFile();
            StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
            string str = "";
            try
            {
                for (int i = 0; i < dataGridView1.ColumnCount; i++)
                {
                    if (i > 0)
                    {
                        str += "\t";
                    }
                    str += dataGridView1.Columns[i].HeaderText;
                }
                sw.WriteLine(str);

                for (int j = 0; j < dataGridView1.Rows.Count; j++)
                {
                    string tempStr = "";
                    for (int k = 0; k < dataGridView1.Columns.Count; k++)
                    {
                        if (k > 0)
                        {
                            tempStr += "\t";
                        }
                        if (dataGridView1.Rows[j].Cells[k].Value != null)
                            tempStr += dataGridView1.Rows[j].Cells[k].Value.ToString();
                    }
                    sw.WriteLine(tempStr);
                }
                sw.Close();
                myStream.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
            }
            finally
            {
                sw.Close();
                myStream.Close();
                MessageBox.Show("Success!!!");
            }
            */

            try
            {
                createDoc();

                //for (int i = 0; i < 1000; i++)
                    //Console.WriteLine(i+":" + getExcelColumnName(i));

                worksheet = (Excel.Worksheet)workbook.Sheets[1];
                for (int i = 0; i < dataGridView1.ColumnCount; i++)
                {
                    addData(0 + 1, i + 1, dataGridView1.Columns[i].HeaderText, getExcelColumnName(i) + "1", getExcelColumnName(i) + "1", "");
                }
                for (int j = 0; j < dataGridView1.Rows.Count; j++)
                {
                    for (int k = 0; k < dataGridView1.Columns.Count; k++)
                    {
                        if (dataGridView1.Rows[j].Cells[k].Value != null)
                            addData(j + 2, k + 1, dataGridView1.Rows[j].Cells[k].Value.ToString(), getExcelColumnName(k) + (j + 2), getExcelColumnName(k) + (j + 2), "");
                    }
                }
                
                worksheet = (Excel.Worksheet)workbook.Sheets[2];
                for (int i = 0; i < dataGridView2.ColumnCount; i++)
                {
                    addData(0 + 1, i + 1, dataGridView2.Columns[i].HeaderText, getExcelColumnName(i) + "1", getExcelColumnName(i) + "1", "");
                }
                for (int j = 0; j < dataGridView2.Rows.Count; j++)
                {
                    for (int k = 0; k < dataGridView2.Columns.Count; k++)
                    {
                        if (dataGridView2.Rows[j].Cells[k].Value != null)
                            addData(j + 2, k + 1, dataGridView2.Rows[j].Cells[k].Value.ToString(), getExcelColumnName(k) + (j + 2), getExcelColumnName(k) + (j + 2), "");
                    }
                }

                worksheet = (Excel.Worksheet)workbook.Sheets[3];
                // TO DO ........................


                //addData(1, 1, "TEST", "A1", "A1", "");

                closeDoc(saveFileDialog.FileName);
                MessageBox.Show("Success!!!");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error::: "+ex.Message);
            }
        }
    }
}